package com.powerManager.sdk.common;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.powerManager.sdk.utils.JsonUtil;

/** -- 分页查询工具类
 * @author lqw
 *
 */
public class PagingResult {
	
	private int totalResult;
	
	private String sql;
	
	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	private void setTotalResult(JdbcTemplate jdbc, String sql, Object[] args) {
		StringBuffer sb = new StringBuffer("select count(1) from (");
		sb.append(sql);
		sb.append(") t");
		this.totalResult = jdbc.queryForInt(sb.toString(),args);
		//this.totalResult = totalResult;
	}

	public PagingResult() {
		super();
	}

	public PagingResult(String sql) {
		super();
		this.sql = sql;
	}

	/** -- 获得总行数
	 * @return totalResult
	 */
	public int getTotalResult() {
		return totalResult;
	}
	
	/** -- 分页查询
	 * @param sql -- 查询SQL语句
	 * @param args -- SQL参数,可以为NULL
	 * @param pageSize --每页多少行数据
	 * @param pageNow -- 当前页数
	 * @return -- List<Map<String, Object>>
	 * @throws Exception
	 */
	public List<Map<String, Object>> paging(String sql, Object[] args, int pageSize,int pageNow) throws Exception{
		JdbcTemplate jdbc = JdbcSingleton.getJdbcTemplate();
		if(sql == null || sql.trim().equals("")){
			throw new Exception("无效的SQL语句:["+sql+"]");
		}
		this.setTotalResult(jdbc, sql, args);
		if(this.totalResult<=0){
			return null;
		}
		StringBuffer temp_sql = new StringBuffer(sql);
		temp_sql.append(" limit ");
		temp_sql.append((pageNow-1)*pageSize);
		temp_sql.append(",");
		temp_sql.append(pageSize);
		System.out.println(temp_sql.toString());
		List<Map<String, Object>> rows = jdbc.queryForList(temp_sql.toString(), args);
		return rows;
	}
	
	public List<Map<String, Object>> paging(Object[] args, int pageSize,int pageNow) throws Exception{
		return this.paging(this.sql, args, pageSize, pageNow);
	}
	
	public List<Map<String, Object>> paging(int pageSize,int pageNow) throws Exception{
		return this.paging(this.sql, null, pageSize, pageNow);
	}
	
	public List<Map<String, Object>> noPaging(String sql, Object[] args) throws Exception{
		JdbcTemplate jdbc = JdbcSingleton.getJdbcTemplate();
		if(sql == null || sql.trim().equals("")){
			throw new Exception("无效的SQL语句:["+sql+"]");
		}
		List<Map<String, Object>> rows = jdbc.queryForList(sql, args);
		this.totalResult = rows.size();
		return rows;
	}

	
	/** -- 分页查询
	 * @param sql -- 查询SQL语句
	 * @param args -- SQL参数,可以为NULL
	 * @param pageSize --每页多少行数据
	 * @param pageNow -- 当前页数
	 * @return -- Json字符 {Total:count,Rows:[{FieldName:field1},{FieldName:field2}]}
	 * @throws Exception
	 */
	public String pagingToJson(String sql, Object[] args, int pageSize,int pageNow) throws Exception{
		List<Map<String, Object>> rows = this.paging(sql, args, pageSize, pageNow);
		StringBuffer json = new StringBuffer("{\"Total\":");
		json.append(this.totalResult);
		json.append(",\"Rows\":");
		json.append(JsonUtil.toJson(rows));
		json.append("}");
		return json.toString();
	}
	
	public String pagingToJson(Object[] args, int pageSize,int pageNow) throws Exception{
		return this.pagingToJson(this.sql, args, pageSize, pageNow);
	}
	
	public String pagingToJson(int pageSize,int pageNow) throws Exception{
		return this.pagingToJson(this.sql, null, pageSize, pageNow);
	}
	
	
	/** -- 将普通数据结构转换成树结构
	 * @param sql -- 
	 * @param args -- 
	 * @param childenKey -- 对应子节点的字段名
	 * @param parentKey -- 对应父节点的字段名
	 * @param rootValue -- 根节点值
	 * @return 树
	 */
	public List<Map<String, Object>> noPagingTree(String sql,Object[] args,String childenKey,String parentKey,String rootValue) throws Exception{
		List<Map<String, Object>> rows = this.noPaging(sql, args);
		List<Map<String, Object>> tree = this.toTree(rows, childenKey, parentKey, rootValue);
		return tree;
	}
	
	/** -- 将普通数据结构转换成树结构
	 * @param childenKey -- 对应子节点的字段名
	 * @param parentKey -- 对应父节点的字段名
	 * @param rootValue -- 根节点值
	 * @return {Rows:[{id:1,pid:0,childen:[{id:2,pid:1}]}]}
	 */
	public String treeToJson(String sql,Object[] args,String childenKey,String parentKey,String rootValue) throws Exception{
		List<Map<String, Object>> rows = this.noPagingTree(sql, args, childenKey, parentKey, rootValue);
		StringBuffer tree = new StringBuffer("{\"Rows\":");
		tree.append(JsonUtil.toJson(rows));
		tree.append("}");
		return tree.toString();
	}
	public String treeToJsonBymodule(String sql,Object[] args,String childenKey,String parentKey,String rootValue) throws Exception{
		List<Map<String, Object>> rows = this.noPagingTree(sql, args, childenKey, parentKey, rootValue);
		StringBuffer tree = new StringBuffer();
		tree.append(JsonUtil.toJson(rows));
		//tree.append("}");
		return tree.toString();
	}
	
	/** -- 将普通数据结构转换成树结构
	 * @param rows -- 源数据
	 * @param tree -- 树结构
	 * @param childenKey -- 对应子节点的字段名
	 * @param parentKey -- 对应父节点的字段名
	 * @param rootValue -- 根节点值
	 * @return 树
	 */
	public List<Map<String, Object>> toTree(List<Map<String, Object>> rows,String childenKey,String parentKey,String rootValue){
		if(rows.isEmpty()) return null;
		List<Map<String, Object>> tree = new ArrayList<Map<String,Object>>();
		Map<String, Map<String, Object>> mapRows = new HashMap<String, Map<String, Object>>();
		for (Map<String, Object> map : rows) {
			if(!map.containsKey(childenKey))continue;
			//LiuC更改如果map.get(childenKey)为字符串转换异常
			String childenValue = String.valueOf(map.get(childenKey));
			mapRows.put(childenValue, map);
		}
		for (Map<String, Object> item : rows)
        {
            if (!item.containsKey(childenKey)) continue;
            if (item.containsKey(parentKey) && item.get(parentKey).equals(rootValue))
            {
                tree.add(item);
            }
            else
            {
            	Map<String, Object> pitem = mapRows.get(item.get(parentKey).toString());
                if (!pitem.containsKey("children"))
                    pitem.put("children", new ArrayList<Map<String,Object>>());
                @SuppressWarnings("unchecked")
				List<Map<String, Object>> children = (List<Map<String, Object>>) pitem.get("children");
                children.add(item);
            }
        }
		return tree;
	}
	
}
